package com.qianfeng.jdbc.dao;


//
//                            _ooOoo_  
//                           o8888888o  
//                           88" . "88  
//                           (| -_- |)  
//                            O\ = /O  
//                        ____/`---'\____  
//                      .   ' \\| |// `.  
//                       / \\||| : |||// \  
//                     / _||||| -:- |||||- \  
//                       | | \\\ - /// | |  
//                     | \_| ''\---/'' | |  
//                      \ .-\__ `-` ___/-. /  
//                   ___`. .' /--.--\ `. . __  
//                ."" '< `.___\_<|>_/___.' >'"".  
//               | | : `- \`.;`\ _ /`;.`/ - ` : | |  
//                 \ \ `-. \_ __\ /__ _/ .-` / /  
//         ======`-.____`-.___\_____/___.-`____.-'======  
//                            `=---='  
//  
//         .............................................  
//                  佛祖镇楼            BUG辟易  
//          佛曰:  
//                  写字楼里写字间，写字间里程序员；  
//                  程序人员写程序，又拿程序换酒钱。  
//                  酒醒只在网上坐，酒醉还来网下眠；  
//                  酒醉酒醒日复日，网上网下年复年。  
//                  但愿老死电脑间，不愿鞠躬老板前；  
//                  奔驰宝马贵者趣，公交自行程序员。  
//                  别人笑我忒疯癫，我笑自己命太贱；  


import com.qianfeng.jdbc.mapper.AccountMapper;
import com.qianfeng.jdbc.pojo.Account;
import com.qianfeng.jdbc.utils.DBUtils;
import com.qianfeng.jdbc.utils.DaoUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by jackiechan on 2021/8/22 16:05
 *
 * @author jackiechan
 * @version 1.0
 * @since 1.0
 */

public class AccountDaoImpl implements AccountDao {

    @Override
    public int addAccount(Account account) throws Exception {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement statement = connection.prepareStatement("insert into account (card_num,username,password,moeny) values(?,?,?,?)");//创建预编译的 statement
//        statement.setString(1, account.getCardNum());
//        statement.setString(2, account.getUsername());
//        statement.setString(3, account.getPassword());
//        statement.setLong(4, account.getMoeny());
//        int result = statement.executeUpdate();
//        DBUtils.closeResources(connection, statement, null);
        String sql = "insert into account (card_num,username,password,moeny) values(?,?,?,?)";
        //将参数转换为数组
        Object[] params = new Object[]{ account.getCardNum(), account.getUsername(),account.getPassword(),account.getMoeny()};
        int result = DaoUtils.commonsUpdate(sql, params);
        System.err.println(result);
        return result;
    }

    /**
     * 更新用户,注意 卡号和用户名是不能更新的
     *
     * @param account
     * @return
     * @throws Exception
     */
//    @Override
//    @Deprecated
//    public int updateAccount(Account account) throws Exception {
//        Connection connection = DBUtils.getConnection();
//        connection.setAutoCommit(false);//开启事务
//        try {
//            PreparedStatement statement = connection.prepareStatement("update  account set password=?,moeny=? where username=?");
//            statement.setString(1, account.getPassword());
//            statement.setLong(2, account.getMoeny());
//            statement.setString(3, account.getUsername());
//            int result = statement.executeUpdate();
////            int i = 1 / 0;
////            if (account.getUsername().equals("lanlan")) {
////                int i = 1 / 0;
////            }
//            connection.commit();//提交事务
//            return result;
//        }catch (Exception e){
//           e.printStackTrace();
//            connection.rollback();//出现问题的时候回滚事务
//        }
//        //todo 关闭连接
//        return 0;
//    }
    @Override
    public int updateAccount(Account account) throws Exception {
//        Connection connection = DBUtils.getConnection();//这边获取到的连接和 service 是不是同一个,肯定是,因为线程没有变过
//        System.err.println("dao"+connection);
//        PreparedStatement statement = connection.prepareStatement("update  account set password=?,moeny=? where username=?");
//        statement.setString(1, account.getPassword());
//        statement.setLong(2, account.getMoeny());
//        statement.setString(3, account.getUsername());
//        int result = statement.executeUpdate();

        String sql = "update  account set password=?,moeny=? where username=?";
        Object[] params = new Object[]{account.getPassword(), account.getMoeny(), account.getUsername()};
        int result = DaoUtils.commonsUpdate(sql, params);
        System.err.println("update  "+result);
//            int i = 1 / 0;
        return result;

    }

    @Override
    @Deprecated
    public int updateAccount(Account account, Connection connection) throws Exception {

        PreparedStatement statement = connection.prepareStatement("update  account set password=?,moeny=? where username=?");
        statement.setString(1, account.getPassword());
        statement.setLong(2, account.getMoeny());
        statement.setString(3, account.getUsername());
        int result = statement.executeUpdate();
        return result;
    }

    @Override
    public int deleteAccount(String userName) throws Exception {
//        Connection connection = DBUtils.getConnection();
//        try {
//            connection.setAutoCommit(false);//关闭自动提交事务,需要手动提交
//            PreparedStatement statement = connection.prepareStatement("delete from account where username =?");
//            statement.setString(1, userName);
//            int result = statement.executeUpdate();
//            int i = 1 / 0;
//            connection.commit();//提交事务
//            return result;
//        } catch (Exception e) {
//            e.printStackTrace();
//            connection.rollback();//回滚事务
//        }

        String sql = "delete from account where username =?";
        Object[] params = new Object[]{userName};
        int result = DaoUtils.commonsUpdate(sql, params);

        System.err.println("delete"+result);
        return result;
    }

    @Override
    public Account findAccountByCardNum(String cardNum) throws Exception {
        Connection connection = DBUtils.getConnection();
        PreparedStatement statement = connection.prepareStatement("select * from account where cardNum =?");
        statement.setString(1, cardNum);
        ResultSet resultSet = statement.executeQuery();
//        Account account = null;
//        if (resultSet.next()) {//因为银行卡号是唯一的,所以能查询到数据的情况下只有一条,可以使用 if 判断
//            account = new Account();
//            account.setCardNum(resultSet.getString("card_num"));
//            account.setUsername(resultSet.getString("username"));
//            account.setPassword(resultSet.getString("password"));
//            account.setMoeny(resultSet.getLong("moeny"));
//        }
        Account account = getAccount(resultSet);
        DBUtils.closeResources(connection, statement, resultSet);
        return account;
    }






    @Override
    public Account findAccountByUserName(String userName) throws Exception {
        Connection connection = DBUtils.getConnection();
        PreparedStatement statement = connection.prepareStatement("select * from account where username =?");
        statement.setString(1, userName);
        ResultSet resultSet = statement.executeQuery();
        Account account = getAccount(resultSet);

        DBUtils.closeResources(connection, statement, resultSet);
        return account;

    }

    @Override
    public void findAccountByUserNameUtils(String userName) throws Exception {
        String sql = "select * from account where username =?";
        DaoUtils.commonsQuery(sql,new Object[]{userName},new AccountMapper());
    }


    private Account getAccount(ResultSet resultSet) throws SQLException {
        Account account = null;
        if (resultSet.next()) {//因为银行卡号是唯一的,所以能查询到数据的情况下只有一条,可以使用 if 判断
            account = new Account();
            account.setCardNum(resultSet.getString("card_num"));
            account.setUsername(resultSet.getString("username"));
            account.setPassword(resultSet.getString("password"));
            account.setMoeny(resultSet.getLong("moeny"));
        }
        return account;
    }
}
